package model;

import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.xml.bind.annotation.XmlRootElement;
import javax.xml.bind.annotation.XmlAccessType;
import javax.xml.bind.annotation.XmlAccessorType;
import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;
/**
 * Класс описывающий некоторую базу данных рейсов
 * @version 1.0
 * @see InterfaceFlights
 */
@XmlRootElement
@XmlAccessorType(XmlAccessType.FIELD)
public class Flights {

    public static int addFlight(Flight newFlight) {
        Connection conn = DataAccess.getConn();
        try{
            PreparedStatement stat = conn.prepareStatement("INSERT INTO flight(aircraft,route,departtime,traveltime) VALUES (?,?,?,?)",Statement.RETURN_GENERATED_KEYS);
            stat.setString(1, newFlight.getAircraft());
            stat.setInt(2, newFlight.getRoute().getID());
            stat.setTime(3, new java.sql.Time(newFlight.getDepartTime().getTime()));
            stat.setTime(4, new java.sql.Time(newFlight.getTravelTime().getTime()));
             stat.executeUpdate();            
            ResultSet rs = stat.getGeneratedKeys();
            rs.next();
            return rs.getInt(1);
        }
        catch(SQLException ex){
             return 0;
        }
        finally{
           
            try {
               
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(Flights.class.getName()).log(Level.SEVERE, null, ex);
                 return 0;
            }            
           
        }
    }

    
    //
    public static boolean checkFlightsNumber(int flightsNumber) {
        Connection conn = DataAccess.getConn();
        boolean res;
        try{
            PreparedStatement stat = conn.prepareStatement("select * from flight where id = ?");
            stat.setInt(1, flightsNumber);
            ResultSet rs = stat.executeQuery();
            res = rs.next();
        }
        catch(SQLException ex){
            res = false;
        }
        finally{
            try {
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(Flights.class.getName()).log(Level.SEVERE, null, ex);
            }            
        }
        return res;     
    }  

    public static ArrayList<Flight> getFlightsFromRS(ResultSet rs){
         ArrayList<Flight> flightsFromRS = new ArrayList<Flight>();
        try {            
            while (rs.next()){
                Flight newFlight = new Flight();
                newFlight.setAircraft(rs.getString("aircraft"));
                newFlight.setFlightNumber(rs.getInt("id"));
                newFlight.setDepartTime(rs.getDate("departTime"));
                newFlight.setTravelTime(rs.getDate("travelTime"));
                Route newRoute = new Route(new Point(rs.getString("outtown"),rs.getString("outairport")),new Point(rs.getString("intown"),rs.getString("inairport")));
                newFlight.setRoute(newRoute);
                flightsFromRS.add(newFlight);
            }            
        } catch (SQLException ex) {
            Logger.getLogger(Flights.class.getName()).log(Level.SEVERE, null, ex);
        }
        return flightsFromRS;
    }
        
    public static ArrayList<Flight> search(int flightsNumber, String aircraft,
            String arriveTown, String arriveAirport,
            String departTown, String departArport, Date departTime) {
        Connection conn = DataAccess.getConn();
        try{
        PreparedStatement stat = conn.prepareStatement(
"select flight.id id,flight.aircraft aircraft,flight.departTime departTime,flight.travelTime travelTime,inpoint.town  intown, outpoint.town outtown,inpoint.airport inairport,outpoint outairport\n"+
 "           from flight\n"+
"            INNER JOIN route on flight.route = route.id\n"+
"            INNER JOIN  point as inpoint on  route.inpoint = inpoint.id\n"+
"            INNER JOIN  point as outpoint on route.outpoint = outpoint.id\n"+
"            where(flight.id = ? or ? = 0)\n"+
"            and (aircraft like ? )\n"+
"            and (inpoint.town like ? )\n"+
"            and (inpoint.airport like ? )\n"+
"            and (outpoint.town like ? )\n"+
"            and (outpoint.airport like ? )\n");
        stat.setInt(1, flightsNumber);
        stat.setInt(2, flightsNumber);
        stat.setString(3, aircraft);
        stat.setString(4, arriveTown);
        stat.setString(5, arriveAirport);
        stat.setString(6, departTown);
        stat.setString(7, departArport);
       
        ResultSet rs = stat.executeQuery();
        return getFlightsFromRS(rs);
        }
        catch(SQLException ex){
            return null;
        }
        finally{
            try {
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(Flights.class.getName()).log(Level.SEVERE, null, ex);
            }            
        }
    }
    
    public static void deleteFlightByFlightsNumber(int flightsNumber) {
      Connection conn = DataAccess.getConn();
        try{
        PreparedStatement stat = conn.prepareStatement("DELETe from flight\n"
            +"WHERE id = ?");
        stat.setInt(1, flightsNumber);        
         stat.executeUpdate();               
        }
        catch(SQLException ex){            
        }
        finally{
            try {
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(Flights.class.getName()).log(Level.SEVERE, null, ex);
            }            
        }  
    }

    public static void updateFlight(Flight newFlight) {
     Connection conn = DataAccess.getConn();
        try{
        PreparedStatement stat = conn.prepareStatement("UPDATE flight\n"
            +" SET aircraft = ?,route = ?, departtime = ?, traveltime = ? \n"
            +"WHERE id = ?");
        stat.setString(1, newFlight.getAircraft());        
        stat.setInt(2, newFlight.getRoute().getID());
        stat.setTime(3, new java.sql.Time(newFlight.getDepartTime().getTime()));
        stat.setTime(4, new java.sql.Time(newFlight.getTravelTime().getTime()));
        
        stat.setInt(5, newFlight.getFlightNumber());
         stat.executeUpdate();               
        }
        catch(SQLException ex){            
        }
        finally{
            try {
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(Flights.class.getName()).log(Level.SEVERE, null, ex);
            }            
        }     
    }
    
     public static Date toDate(String hour, String min) {
        GregorianCalendar time = new GregorianCalendar();
        time.set(0, 0, 0, Integer.parseInt(hour), Integer.parseInt(min), 0);
        time.set(Calendar.MILLISECOND, 0);
        return time.getTime();
    }

}
